package tgc.edu.tms.web.controller;

import java.io.IOException;
import java.io.OutputStream;
import java.math.BigInteger;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;

import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.util.Base64Utils;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import tgc.edu.tms.custom.ExportExcelUtils;
import tgc.edu.tms.custom.TablePageable;
import tgc.edu.tms.entity.FlowTable;
import tgc.edu.tms.entity.WorkSheetCount;
import tgc.edu.tms.service.FlowTableService;
import tgc.edu.tms.web.form.WorkSheetCountForm;

/**
 * @className: WorkSheetCount
 * @description: 按月工作量统计控制器，未合并单元格
 * @author: JavaJun
 * @date: 2019年1月3日
 */
@Controller
@RequestMapping(value = "/worksheetcount")
public class WorkSheetCountController {

	@Autowired
	private FlowTableService flowTableService;

	@RequestMapping(value = "/manage")
	public void manage(ModelMap map) {
		List<String> allTime = allTime();
		map.put("allTime", allTime);
	}

	@RequestMapping(value = "/export")
	@ResponseBody
	public void export(HttpServletRequest request, HttpServletResponse response, String time) throws Exception {
		if (time==null||time==null) {
			DateFormat format = new SimpleDateFormat("yyyy-MM");
			time=format.format(new Date());
		}
		List<String> t = flowTableService.findTeacher();
		String title = "月工作量统计表";
		String[] columnName = new String[] { "序号", "教师", "课程", "上课班级", "计划学时", "月分班学时", "学生人数", "折合系数", "分班折合学时",
				"月完成总学时", "月折合总学时", "备注" };
		ExportExcelUtils excel = new ExportExcelUtils();
		try {
			HSSFWorkbook workbook = new HSSFWorkbook(); // 创建工作簿对象
			HSSFSheet sheet = workbook.createSheet(title); // 创建工作表

			// 产生表格标题行
			HSSFRow rowm = sheet.createRow(0);
			HSSFCell cellTiltle = rowm.createCell(0);

			// 设置标题和单元格样式
			HSSFCellStyle columnTopStyle = excel.getColumnTopStyle(workbook); // 获取列头样式对象
			HSSFCellStyle style = excel.getStyle(workbook); // 单元格样式对象

			// 合并单元格
			sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (columnName.length - 1)));
			cellTiltle.setCellStyle(columnTopStyle);
			cellTiltle.setCellValue(title);

			// 定义所需列数
			int columnNum = columnName.length;
			HSSFRow rowRowName = sheet.createRow(2); // 在索引2的位置创建行(最顶端的行开始的第二行)

			// 将列头设置到sheet的单元格中
			for (int n = 0; n < columnNum; n++) {
				HSSFCell cellRowName = rowRowName.createCell(n); // 创建列头对应个数的单元格
				cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING); // 设置列头单元格的数据类型
				HSSFRichTextString text = new HSSFRichTextString(columnName[n]);
				cellRowName.setCellValue(text); // 设置列头单元格的值
				cellRowName.setCellStyle(columnTopStyle); // 设置列头单元格样式
			}

			// 将查询出的数据设置到sheet对应的单元格中
			int a = 0, m = 3;
			for (String Teacher : t) {
				Double y = (double) 0;
				int x = 0;
				List<Object[]> cout = flowTableService.findCout(time, Teacher);
				for (int i = 0; i < cout.size(); i++) {
					Object[] z = cout.get(i);
					BigInteger q = (BigInteger) z[6];
					Integer p = q.intValue();
					WorkSheetCount workSheet = new WorkSheetCount((String) z[0], (String) z[1], (String) z[2],
							(Integer) z[3], (Boolean) z[5], (Integer) z[4], p);
					HSSFRow row = sheet.createRow(a + 3);// 创建所需的行数

					HSSFCell cell = null; // 设置单元格的数据类型
					// 第一列为数字类型并设置单元格的值

					cell = row.createCell(0, HSSFCell.CELL_TYPE_NUMERIC);
					cell.setCellValue(a + 1);
					cell.setCellStyle(style);
					// 其他列为字符串类型并设置单元格的值
					cell = row.createCell(2, HSSFCell.CELL_TYPE_STRING);
					cell.setCellValue(workSheet.getCourse());
					cell.setCellStyle(style);
					cell = row.createCell(3, HSSFCell.CELL_TYPE_STRING);
					cell.setCellValue(workSheet.getTbclass());
					cell.setCellStyle(style);
					cell = row.createCell(4, HSSFCell.CELL_TYPE_STRING);
					cell.setCellValue(workSheet.getJhxs());
					cell.setCellStyle(style);
					cell = row.createCell(5, HSSFCell.CELL_TYPE_STRING);
					cell.setCellValue(workSheet.getYfbxs());
					x = x + workSheet.getYfbxs();
					cell.setCellStyle(style);
					cell = row.createCell(6, HSSFCell.CELL_TYPE_STRING);
					cell.setCellValue(workSheet.getXsrs());
					cell.setCellStyle(style);
					cell = row.createCell(7, HSSFCell.CELL_TYPE_STRING);
					cell.setCellValue(workSheet.getZhxs());
					cell.setCellStyle(style);
					cell = row.createCell(8, HSSFCell.CELL_TYPE_STRING);
					cell.setCellValue(workSheet.getFbzhxs());
					y = y + workSheet.getFbzhxs();
					cell.setCellStyle(style);
					cell = row.createCell(11, HSSFCell.CELL_TYPE_STRING);
					if (workSheet.getXmh() != null) {
						if (workSheet.getXmh()) {
							cell.setCellValue("项目化课程折合系数1.2");
							cell.setCellStyle(style);
						} else {
							cell.setCellValue("-");
							cell.setCellStyle(style);
						}
					} else {
						cell.setCellValue("-");
						cell.setCellStyle(style); // 设置单元格样式
					}
					a = a + 1;
				}
				if (cout.size() > 0) {
					HSSFRow rowt = sheet.getRow(m);
					HSSFCell teacher = rowt.createCell(1);
					HSSFCell ywczxs = rowt.createCell(9);
					HSSFCell yzhzxs = rowt.createCell(10);
					if (cout.size() > 1) {
						sheet.addMergedRegion(new CellRangeAddress(m, (m + cout.size() - 1), 1, 1));
						sheet.addMergedRegion(new CellRangeAddress(m, (m + cout.size() - 1), 9, 9));
						sheet.addMergedRegion(new CellRangeAddress(m, (m + cout.size() - 1), 10, 10));
						m = m + cout.size();
					} else {
						m = m + 1;
					}
					teacher.setCellValue(Teacher);
					ywczxs.setCellValue(x);
					yzhzxs.setCellValue(y);
					teacher.setCellStyle(style);
					ywczxs.setCellStyle(style);
					yzhzxs.setCellStyle(style);
				}
			}

			// 让列宽随着导出的列长自动适应
			for (int colNum = 0; colNum < columnNum; colNum++) {
				int columnWidth = sheet.getColumnWidth(colNum) / 256;
				for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
					HSSFRow currentRow;
					// 当前行未被使用过
					if (sheet.getRow(rowNum) == null) {
						currentRow = sheet.createRow(rowNum);
					} else {
						currentRow = sheet.getRow(rowNum);
					}
					if (currentRow.getCell(colNum) != null) {
						// 取得当前的单元格
						HSSFCell currentCell = currentRow.getCell(colNum);
						// 如果当前单元格类型为字符串
						if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
							int length = currentCell.getStringCellValue().getBytes().length;
							if (columnWidth < length) {
								// 将单元格里面值大小作为列宽度
								columnWidth = length;
							}
						}
					}
				}
				// 再根据不同列单独做下处理
				if (colNum == 0) {
					sheet.setColumnWidth(colNum, (columnWidth - 2) * 256);
				} else {
					sheet.setColumnWidth(colNum, (columnWidth + 4) * 256);
				}
			}

			if (workbook != null) {
				try {
					String agent = request.getHeader("USER-AGENT");
					String fileName = "Excel-" + time + "工作量统计表.xls";
					if (agent != null && agent.toLowerCase().indexOf("firefox") > 0) {
						fileName = "=?UTF-8?B?" + Base64Utils.encodeToString(fileName.getBytes("UTF-8")) + "?=";
					}
					String headStr = "attachment; filename=\"" + fileName + "\"";
					response.setContentType("APPLICATION/OCTET-STREAM");
					response.setHeader("Content-Disposition", headStr);
					OutputStream out1 = response.getOutputStream();
					workbook.write(out1);
					workbook.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}

		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	@RequestMapping(value = "/page")
	@ResponseBody
	private HashMap<String, Object> page(TablePageable pageParam, WorkSheetCountForm form) {
		// 如果没有查询时间，则默认查询当月的记录
		if (!StringUtils.hasText(form.getTime())) {
			DateFormat format = new SimpleDateFormat("yyyy-MM");
			form.setTime(format.format(new Date()));
		}

		// 创建前台所需的数据
		HashMap<String, Object> result = new HashMap<>();

		// 所有老师
		List<String> allTeacher = allTeacher(form);
		List<FlowTable> monOneTeaOneClassOneLog = new ArrayList<>();
		for (String s : allTeacher) {
			// System.out.println("老师："+s);
			WorkSheetCountForm wform1 = new WorkSheetCountForm();
			BeanUtils.copyProperties(form, wform1);
			wform1.setTeacher(s);
			List<FlowTable> oneTeacherAllLog = flowTableService.findAll(buildSpec(wform1));
			List<FlowTable> oneTeaOneClassOneLog = new ArrayList<>();
			for (FlowTable ont : oneTeacherAllLog) {
				Boolean flag = false;
				for (FlowTable otcl : oneTeaOneClassOneLog) {
					if (ont.getClasses().equals(otcl.getClasses()) && ont.getTeacher().equals(otcl.getTeacher())
							&& ont.getCourse().equals(otcl.getCourse())) {
						flag = true;
						break;
					}
				}

				if (!flag) {
					oneTeaOneClassOneLog.add(ont);
				}
			}

			for (FlowTable f : oneTeaOneClassOneLog) {
				monOneTeaOneClassOneLog.add(f);
				// System.out.println("老师："+f.getTeacher()+"\t班级："+f.getClasses()+"\t课程："+f.getCourse());
			}
		}

		// 当月所有记录
		/*
		 * List<FlowTable> monAllLog = flowTableService.findAll(buildSpec(form));
		 * 
		 * // 当月每一个老师一个班级一条记录 List<FlowTable> monOneTeaOneClassOneLog = new
		 * ArrayList<>(); for (FlowTable flowTable1 : monAllLog) { // String classes1 =
		 * flowTable1.getClasses(); //String teacher1 = flowTable1.getTeacher();
		 * //String course1 = flowTable1.getCourse(); Boolean flag = true; for(FlowTable
		 * flowTable2 : monOneTeaOneClassOneLog) {
		 * if((flowTable1.getClasses().equals(flowTable2.getClasses()) &&
		 * flowTable1.getTeacher().equals(flowTable2.getTeacher())) &&
		 * flowTable1.getCourse().equals(flowTable2.getCourse())) { flag = false; break;
		 * } } if(flag) { monOneTeaOneClassOneLog.add(flowTable1); } }
		 */
		// 数据总数（分页total）
		Integer count = 0;
		List<WorkSheetCount> content = new ArrayList<>();
		for (int k = 0; k < monOneTeaOneClassOneLog.size(); k++) {
			count += 1;
			WorkSheetCount workSheetCount = new WorkSheetCount();

			FlowTable flowTable = monOneTeaOneClassOneLog.get(k);
			WorkSheetCountForm searchTeacher = new WorkSheetCountForm();
			BeanUtils.copyProperties(form, searchTeacher);
			String classes = flowTable.getClasses();// 班级
			String teacher = flowTable.getTeacher();// 老师
			String course = flowTable.getCourse();// 课程
			String time = flowTable.getTime();// 时间(某一节课的上课时间，无用)
			Integer jhxs = flowTable.getZxs();// 计划学时
			Integer xsrs = flowTable.getYdrs();// 学生人数
			Double zhxs = flowTable.getZhxs();// 折合系数

			// 设置安全查询用的Form
			searchTeacher.setClasses(classes);
			searchTeacher.setTeacher(teacher);
			searchTeacher.setCourse(course);

			List<FlowTable> oneTeacherOneLogCount = flowTableService.findAll(buildSpec(searchTeacher));

			// 时间
			// 月分班学时
			Integer yfbxs = oneTeacherOneLogCount.size();
			// 月分班折合学时
			Double fbzhxs = yfbxs * zhxs;

			workSheetCount.setId(count);
			workSheetCount.setTime(time);
			workSheetCount.setTbclass(classes);
			workSheetCount.setTeacher(teacher);
			workSheetCount.setCourse(course);
			workSheetCount.setJhxs(jhxs);
			workSheetCount.setXsrs(xsrs);
			workSheetCount.setZhxs(zhxs);
			workSheetCount.setYfbxs(yfbxs);
			workSheetCount.setFbzhxs(fbzhxs);
			content.add(workSheetCount);
		}

		result.put("total", count);
		result.put("rows", content);
		return result;
	}

	/**
	 * 根据老师进行分组，如果老师名字一样，则会出现逻辑问题 出现逻辑问题的原因在于教室在数据库中是一个字符串，老师名字一致时，无法判断不是同一老师
	 * 
	 * @param form
	 * @return
	 */
	private List<FlowTable> allTeacherOneLog(WorkSheetCountForm form) {
		WorkSheetCountForm workSheetCountForm = new WorkSheetCountForm();
		BeanUtils.copyProperties(form, workSheetCountForm);
		workSheetCountForm.setGroupBy("1");
		List<FlowTable> allteacher = flowTableService.findAll(buildSpec(workSheetCountForm));
		return allteacher;
	}

	private List<String> allTeacher(WorkSheetCountForm form) {
		List<FlowTable> allTeacherOneLog = allTeacherOneLog(form);
		List<String> allTeacher = new ArrayList<>();
		for (FlowTable al : allTeacherOneLog) {
			Boolean flag = false;
			for (String s : allTeacher) {
				if (s.equals(al.getTeacher())) {
					flag = true;
					break;
				}
			}
			if (!flag) {
				allTeacher.add(al.getTeacher());
			}
		}
		return allTeacher;
	}

	private List<String> allTime() {
		List<FlowTable> alltimeFlow = flowTableService.findAll(buildSpecForTime());

		List<String> allTime = new ArrayList<>();
		for (FlowTable flowTable : alltimeFlow) {
			String time = flowTable.getTime();
			String ntime = "";
			try {
				String[] timesp = time.split("-");
				ntime = timesp[0] + "-" + timesp[1];
			} catch (Exception e) {

			}
			allTime.add(ntime);
		}

		List<String> allTimeOne = new ArrayList<>();
		for (String time : allTime) {
			Boolean flag = false;

			for (String timeO : allTimeOne) {
				if (time.equals(timeO)) {
					flag = true;
					break;
				}
			}

			if (!flag) {
				allTimeOne.add(time);
			}
		}
		return allTimeOne;
	}

	private Specification<FlowTable> buildSpecForTime() {
		Specification<FlowTable> spec = new Specification<FlowTable>() {

			private static final long serialVersionUID = 1L;

			@Override
			public Predicate toPredicate(Root<FlowTable> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
				HashSet<Predicate> rules = new HashSet<>();
				query.groupBy(root.get("time"));

				return query.getRestriction();
			}
		};
		return spec;
	}

	private Specification<FlowTable> buildSpec(WorkSheetCountForm form) {
		Specification<FlowTable> spec = new Specification<FlowTable>() {
			private static final long serialVersionUID = 1L;

			@Override
			public Predicate toPredicate(Root<FlowTable> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
				HashSet<Predicate> rules = new HashSet<>();
				// 上课内容不为空
				rules.add(cb.isNotNull(root.get("sknr")));

				// 根据老师名字 group by
				if (StringUtils.hasText(form.getGroupBy())) {
					query.groupBy(root.get("teacher"));
				}

				// 上课时间
				String time = form.getTime();
				if (StringUtils.hasText(time)) {
					rules.add(cb.like(root.get("time"), "%" + time + "%"));
				}
				String classes = form.getClasses();
				if (StringUtils.hasText(classes)) {
					rules.add(cb.equal(root.get("classes"), classes));
				}
				String teacher = form.getTeacher();
				if (StringUtils.hasText(teacher)) {
					rules.add(cb.equal(root.get("teacher"), teacher));
				}
				String course = form.getCourse();
				if (StringUtils.hasText(course)) {
					rules.add(cb.equal(root.get("course"), course));
				}
				query.where(cb.and(rules.toArray(new Predicate[rules.size()])));

				return query.getRestriction();
			}
		};
		return spec;
	}
}
